﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web.Configuration;
public class DB
{
    private SqlConnection con = null;
    private string strConn;
    private SqlTransaction tran;
    public DB()
    {
        strConn = WebConfigurationManager.ConnectionStrings["crmConnectionString"].ToString();
    }
    //打开数据库连接
    public void open()
    {
        if (con == null)
            con = new SqlConnection(strConn);
        if (con.State.Equals(ConnectionState.Closed))
            con.Open();
    }
    //关闭数据库
    public void close()
    {
        if (con == null)
            return;
        if (con.State.Equals(ConnectionState.Open))
        {
            con.Close();
            con.Dispose();
            con = null;
        }
        else
        {
            con.Dispose();
            con = null;
        }
    }
    //执行SQL语句，如果bTran=true则不关闭连接，事务处理使用
    public int ExecuteSQLNonQuery(string sql, List<SqlParameter> sqlParams, bool bTran = false)
    {
        int ret = 0;
        try
        {
            this.open();//打开连接
            SqlCommand cmd = new SqlCommand(sql, con);
            if (sqlParams != null)
                foreach (SqlParameter p in sqlParams)
                    cmd.Parameters.Add(p);
            if (bTran)
                cmd.Transaction = tran;
            ret = cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            if (!bTran)
                close();
        }
        return ret;
    }
    //执行SQL语句,返回查询的表
    public DataTable GetDataTable(string sqlStr)
    {
        DataTable dt;
        try
        {
            open();
            SqlDataAdapter sda = new SqlDataAdapter(sqlStr, con);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            dt = ds.Tables[0];
        }
        catch (Exception ex)
        {
            dt = null;
        }
        finally
        {
            close();
        }
        return dt;
    }
    //执行SQL语句,返回DataRow
    public DataRow GetDataRow(string sqlStr)
    {
        DataRow dr;
        try
        {//调用该类GetDataTable方法
            dr = GetDataTable(sqlStr).Rows[0];
        }
        catch
        {
            dr = null;
        }
        finally
        {
            close();
        }
        return dr;
    }
    public void BeginTrans()
    {
        tran = con.BeginTransaction();
    }
    public void Commit()
    {
        tran.Commit();
    }
    public void Rollback()
    {
        tran.Rollback();
    }
}
